<?php
class m_reportdetail extends CI_Model {
	
	function data(){
		$sql = "SELECT project_code, project_name, 
	IFNULL (flag_ppb, '-') flag_ppb, 
	IFNULL (flag_pdp, '-') flag_pdp, 
	IFNULL (flag_pdk, '-') flag_pdk, 
	IFNULL (flag_rp, '-') flag_rp, 
	IFNULL (flag_rapk, '-') flag_rapk, 
	IFNULL (flag_ak, '-') flag_ak,
	IFNULL (flag_bc, '-')  flag_bc   FROM t_project 
LEFT JOIN (
		SELECT t_project_id, 
		if (max( posisi)- 1 >=  maxpos , 'Selesai', if (  posisi is null , '-', div_name ) ) flag_ppb
		FROM v_posdoc left join t_document
			on v_posdoc.document_id = t_document.document_id
			left join m_flowdet
			on posisi = flowdet_seq and v_posdoc.m_flow_id = m_flowdet.m_flow_id
			left join m_div
			on div_id = m_div_id where flow_code = 'PPB' group by v_posdoc.t_project_id
) t_ppb  ON project_id = t_ppb.t_project_id

LEFT JOIN ( SELECT t_project_id, 
		if (max( posisi)- 1 >=  maxpos , 'Selesai', if (  posisi is null , '-', div_name ) ) flag_pdp
		FROM v_posdoc left join t_document
			on v_posdoc.document_id = t_document.document_id
			left join m_flowdet
			on posisi = flowdet_seq and v_posdoc.m_flow_id = m_flowdet.m_flow_id
			left join m_div
			on div_id = m_div_id where flow_code = 'PDP' group by  v_posdoc.t_project_id
) t_pdp  ON project_id = t_pdp.t_project_id

			
LEFT JOIN ( SELECT v_posdoc.t_project_id,  
			GROUP_CONCAT(if (posisi - 1 >= maxpos , SUBSTR(document_no,LENGTH(document_no)-1,2), if ( posisi is null , '-', 
			div_name
 ) )) flag_pdk
			
			FROM v_posdoc 
			left join t_document
			on v_posdoc.document_id = t_document.document_id
			left join m_flowdet
			on posisi = flowdet_seq and v_posdoc.m_flow_id = m_flowdet.m_flow_id
			left join m_div
			on div_id = m_div_id 
			where flow_code = 'PDK' group by v_posdoc.t_project_id
) t_pdk  ON project_id = t_pdk.t_project_id
			
LEFT JOIN ( SELECT v_posdoc.t_project_id,  
			GROUP_CONCAT(if (posisi - 1 >= maxpos , SUBSTR(document_no,LENGTH(document_no)-1,2), if ( posisi is null , '-', 
			div_name
 ) )) flag_rp
			
			FROM v_posdoc 
			left join t_document
			on v_posdoc.document_id = t_document.document_id
			left join m_flowdet
			on posisi = flowdet_seq and v_posdoc.m_flow_id = m_flowdet.m_flow_id
			left join m_div
			on div_id = m_div_id 
			where flow_code = 'RP' group by v_posdoc.t_project_id
) t_rp  ON project_id = t_rp.t_project_id
			
LEFT JOIN ( SELECT v_posdoc.t_project_id,  
			GROUP_CONCAT(if (posisi - 1 >= maxpos , SUBSTR(document_no,LENGTH(document_no)-1,2), if ( posisi is null , '-', 
			div_name
 ) )) flag_rapk
			
			FROM v_posdoc 
			left join t_document
			on v_posdoc.document_id = t_document.document_id
			left join m_flowdet
			on posisi = flowdet_seq and v_posdoc.m_flow_id = m_flowdet.m_flow_id
			left join m_div
			on div_id = m_div_id 
			where flow_code = 'RAPK' group by v_posdoc.t_project_id
) t_rapk  ON project_id = t_rapk.t_project_id
			
LEFT JOIN ( SELECT v_posdoc.t_project_id,  
			GROUP_CONCAT(if (posisi - 1 >= maxpos , SUBSTR(document_no,LENGTH(document_no)-1,2), if ( posisi is null , '-', 
			div_name
 ) )) flag_ak
			
			FROM v_posdoc 
			left join t_document
			on v_posdoc.document_id = t_document.document_id
			left join m_flowdet
			on posisi = flowdet_seq and v_posdoc.m_flow_id = m_flowdet.m_flow_id
			left join m_div
			on div_id = m_div_id 
			where flow_code = 'AK' group by v_posdoc.t_project_id
) t_ak  ON project_id = t_ak.t_project_id
			
LEFT JOIN ( SELECT v_posdoc.t_project_id,  
			GROUP_CONCAT(if (posisi - 1 >= maxpos , SUBSTR(document_no,LENGTH(document_no)-1,2), if ( posisi is null , '-', 
			div_name
 ) )) flag_bc
			
			FROM v_posdoc 
			left join t_document
			on v_posdoc.document_id = t_document.document_id
			left join m_flowdet
			on posisi = flowdet_seq and v_posdoc.m_flow_id = m_flowdet.m_flow_id
			left join m_div
			on div_id = m_div_id 
			where flow_code = 'BC' group by v_posdoc.t_project_id
) t_bc	  ON project_id = t_bc.t_project_id		";
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
	
	function data_detail($project_id){
		$sql = "SELECT project_code, project_name, 
	IFNULL (flag_ppb, '-') flag_ppb, 
	IFNULL (flag_pdp, '-') flag_pdp, 
	IFNULL (flag_pdk, '-') flag_pdk, 
	IFNULL (flag_rp, '-') flag_rp, 
	IFNULL (flag_rapk, '-') flag_rapk, 
	IFNULL (flag_ak, '-') flag_ak,
	IFNULL (flag_bc, '-')  flag_bc,
    
 	IFNULL (t_ppb.tracking_flag, '-') tflag_ppb, 
	IFNULL (t_pdp.tracking_flag, '-') tflag_pdp, 
	IFNULL (t_pdk.tracking_flag, '-') tflag_pdk, 
	IFNULL (t_rp.tracking_flag, '-') tflag_rp, 
	IFNULL (t_rapk.tracking_flag, '-') tflag_rapk, 
	IFNULL (t_ak.tracking_flag, '-') tflag_ak,
	IFNULL (t_bc.tracking_flag, '-')  tflag_bc
    
    FROM t_project 
LEFT JOIN (
		SELECT  v_posdoc.t_project_id, tracking_flag, 
		if (max( posisi)- 1 >=  maxpos , 'Selesai', if (  posisi is null , '-', div_name ) ) flag_ppb
		FROM v_posdoc left join t_document
			on v_posdoc.document_id = t_document.document_id
			left join m_flowdet
			on posisi = flowdet_seq and v_posdoc.m_flow_id = m_flowdet.m_flow_id
			left join m_div
			on div_id = m_div_id where flow_code = 'PPB' group by  v_posdoc.t_project_id
) t_ppb  ON project_id = t_ppb.t_project_id

LEFT JOIN ( SELECT  v_posdoc.t_project_id, tracking_flag, 
		if (max( posisi)- 1 >=  maxpos , 'Selesai', if (  posisi is null , '-', div_name ) ) flag_pdp
		FROM v_posdoc left join t_document
			on v_posdoc.document_id = t_document.document_id
			left join m_flowdet
			on posisi = flowdet_seq and v_posdoc.m_flow_id = m_flowdet.m_flow_id
			left join m_div
			on div_id = m_div_id where flow_code = 'PDP' group by  v_posdoc.t_project_id
) t_pdp  ON project_id = t_pdp.t_project_id

			
LEFT JOIN ( SELECT v_posdoc.t_project_id,tracking_flag,   
			GROUP_CONCAT(if (posisi - 1 >= maxpos , CONCAT(SUBSTR(document_no,LENGTH(document_no)-1,2),' Selesai'), if ( posisi is null , '-', 
			div_name
 ) )) flag_pdk
			
			FROM v_posdoc 
			left join t_document
			on v_posdoc.document_id = t_document.document_id
			left join m_flowdet
			on posisi = flowdet_seq and v_posdoc.m_flow_id = m_flowdet.m_flow_id
			left join m_div
			on div_id = m_div_id 
			where flow_code = 'PDK' group by v_posdoc.t_project_id
) t_pdk  ON project_id = t_pdk.t_project_id
			
LEFT JOIN ( SELECT v_posdoc.t_project_id,  tracking_flag, 
			GROUP_CONCAT(if (posisi - 1 >= maxpos , CONCAT(SUBSTR(document_no,LENGTH(document_no)-1,2),' Selesai'), if ( posisi is null , '-', 
			div_name
 ) )) flag_rp
			
			FROM v_posdoc 
			left join t_document
			on v_posdoc.document_id = t_document.document_id
			left join m_flowdet
			on posisi = flowdet_seq and v_posdoc.m_flow_id = m_flowdet.m_flow_id
			left join m_div
			on div_id = m_div_id 
			where flow_code = 'RP' group by v_posdoc.t_project_id
) t_rp  ON project_id = t_rp.t_project_id
			
LEFT JOIN ( SELECT v_posdoc.t_project_id,  tracking_flag, 
			GROUP_CONCAT(if (posisi - 1 >= maxpos , CONCAT(SUBSTR(document_no,LENGTH(document_no)-1,2),' Selesai'), if ( posisi is null , '-', 
			div_name
 ) )) flag_rapk
			
			FROM v_posdoc 
			left join t_document
			on v_posdoc.document_id = t_document.document_id
			left join m_flowdet
			on posisi = flowdet_seq and v_posdoc.m_flow_id = m_flowdet.m_flow_id
			left join m_div
			on div_id = m_div_id 
			where flow_code = 'RAPK' group by v_posdoc.t_project_id
) t_rapk  ON project_id = t_rapk.t_project_id
			
LEFT JOIN ( SELECT v_posdoc.t_project_id,  tracking_flag, 
			GROUP_CONCAT(if (posisi - 1 >= maxpos , CONCAT(SUBSTR(document_no,LENGTH(document_no)-1,2),' Selesai'), if ( posisi is null , '-', 
			div_name
 ) )) flag_ak
			
			FROM v_posdoc 
			left join t_document
			on v_posdoc.document_id = t_document.document_id
			left join m_flowdet
			on posisi = flowdet_seq and v_posdoc.m_flow_id = m_flowdet.m_flow_id
			left join m_div
			on div_id = m_div_id 
			where flow_code = 'AK' group by v_posdoc.t_project_id
) t_ak  ON project_id = t_ak.t_project_id
			
LEFT JOIN ( SELECT v_posdoc.t_project_id,  tracking_flag, 
			GROUP_CONCAT(if (posisi - 1 >= maxpos , CONCAT(SUBSTR(document_no,LENGTH(document_no)-1,2),' Selesai'), if ( posisi is null , '-', 
			div_name
 ) )) flag_bc
			
			FROM v_posdoc 
			left join t_document
			on v_posdoc.document_id = t_document.document_id
			left join m_flowdet
			on posisi = flowdet_seq and v_posdoc.m_flow_id = m_flowdet.m_flow_id
			left join m_div
			on div_id = m_div_id 
			where flow_code = 'BC' group by v_posdoc.t_project_id
) t_bc	  ON project_id = t_bc.t_project_id	WHERE project_id = '".$project_id."'	";
		$query = $this->db->query($sql);
		$result = $query->row();
		return $result;
	}
}